package com.easidea.tim.dao.saleorder;

import java.io.UnsupportedEncodingException;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.springside.core.dao.extend.HibernateEntityExtendDao;

import com.easidea.tim.entity.saleorder.SaleOrder;
import com.easidea.tim.entity.saleorder.SaleOrderItem;
import com.easidea.tim.util.DateUtil;

public class SaleOrderDao extends HibernateEntityExtendDao<SaleOrder> {

	/**
	 * 按产品统计
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> statSaleOrderByProduct(String customerName,
			String staffName, Date startDate, Date endDate) {
		// String hql = "select item.productName,item.productColor,"
		// + "SUM(item.piNum) as piNum, "
		// + "SUM(item.totalMeter) as totalMeter,"
		// + "SUM(item.totalMeter*item.moneyNum) as moneyNum"
		// + " from "
		// + SaleOrderItem.class.getName()
		// + " as item,"
		// + SaleOrder.class.getName()
		// + " as order where order.deleted = 0 and order.status='4' and
		// order.id = item.saleOrder.id and order.modifyTime >=:startDate and
		// order.modifyTime <=:endDate group by item.product.id ";
		String hql = "select name,color,piNUm,totalMeter,moneyNum from ((select item.product_id as pid,item.productName as name,item.productColor as color,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) "
				+ "as moneyNum from  tim.tim_salesoder_order_item as item, tim.tim_salesoder_order as order1 where  order1.deleted = 0 "
				+ " and order1.status='4' and  order1.id = item.saleOrder_id "
				+ " and order1.modified_time >=:startDate and order1.modified_time <=:endDate and order1.orderType<>'1' and  order1.orderType<>'3'"
				+ "group by item.product_id) UNION (select item.product_id as pid,item.productName as name,item.productColor as color,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) "
				+ "as moneyNum from  tim.tim_salesoder_order_item as item, tim.tim_salesoder_order as order1 where  order1.deleted = 0 "
				+ " and order1.status='4' and  order1.id = item.saleOrder_id "
				+ " and order1.modified_time >=:startDate and order1.modified_time <=:endDate and order1.orderType<>'0' and order1.orderType<>'2'"
				+ "group by item.product_id)) tt order by tt.pid";

		Query query = this.getSession().createSQLQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();

	}

	/**
	 * 客户统计明细
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> getcustomerDetail(Date startDate, Date endDate,
			String id) {
		// String sql = "select
		// item.productName,item.productColor,sum(item.piNum) as piNum,
		// sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum)
		// as moneyNum"
		// + " from "
		// + SaleOrderItem.class.getName()
		// + " as item,"
		// + SaleOrder.class.getName()
		// + " as order where order.deleted = 0 and order.status='4' and
		// order.id = item.saleOrder.id and order.customer.id=:customerid and
		// order.modifyTime >=:startDate and order.modifyTime <=:endDate group
		// by item.product.id ";
		String hql = "select name,color,piNUm,totalMeter,moneyNum from ((select item.product_id as pid,item.productName as name,item.productColor as color,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) "
				+ " as moneyNum from  tim.tim_salesoder_order_item as item, tim.tim_salesoder_order as order1 where  order1.deleted = 0 "
				+ " and order1.status='4' and  order1.id = item.saleOrder_id "
				+ " and order1.modified_time >=:startDate and order1.modified_time <=:endDate and order1.orderType<>'1' and  order1.orderType<>'3' and order1.customer_id=:customerid"
				+ " group by item.product_id) UNION (select item.product_id as pid,item.productName as name,item.productColor as color,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) "
				+ " as moneyNum from  tim.tim_salesoder_order_item as item, tim.tim_salesoder_order as order1 where  order1.deleted = 0 "
				+ " and order1.status='4' and  order1.id = item.saleOrder_id "
				+ " and order1.modified_time >=:startDate and order1.modified_time <=:endDate and order1.orderType<>'0' and order1.orderType<>'2' and order1.customer_id=:customerid"
				+ " group by item.product_id)) tt order by tt.pid";

		Query query = this.getSession().createSQLQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		query.setString("customerid", id);

		return query.list();

	}

	/**
	 * 客户统计明细
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> getcreatorDetail(Date startDate, Date endDate,
			String id) {
		String hql = "select item.productName,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0 and order.status='4' and  order.id = item.saleOrder.id and order.creator=:customerid  and order.modifyTime >=:startDate and order.modifyTime <=:endDate  group by item.productName ";

		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		query.setString("customerid", id);

		return query.list();

	}

	/**
	 * 客户统计明细
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> getcustomerDetailSimple(Date startDate, Date endDate,
			String id, String staffName) {
		String staffSql = "";
		if (!staffName.trim().equals("")) {
			staffSql = " and order1.creator=:creator";
		}
		String hql = "select item.productName,sum(item.piNum) as piNum,sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where order.deleted = 0 and order.status='4' and order.id = item.saleOrder.id"
				+ staffSql
				+ " and order.customer.id=:customerid and order.modifyTime >=:startDate and order.modifyTime <=:endDate group by item.productName ";

		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		query.setString("customerid", id);
		if (!staffName.trim().equals("")) {
			query.setString("creator", staffName);
		}
		return query.list();

	}

	// public List<Object[]> getcustomerDetailSimple(Date startDate, Date
	// endDate,
	// String id, String staffName) {
	// String staffSql = "";
	// if (!staffName.trim().equals("")) {
	// staffSql = " and order1.creator=:creator";
	// }
	// String hql = "select * from ((select item.productName as
	// name,sum(item.piNum) as piNum, sum(item.totalMeter) as
	// totalMeter,sum(item.totalMeter*item.moneyNum) "
	// + "as moneyNum from tim.tim_salesoder_order_item as item,
	// tim.tim_salesoder_order as order1 where order1.deleted = 0 "
	// + staffSql
	// + " and order1.status='4' and order1.id = item.saleOrder_id "
	// + "and order1.customer_id=:customerid and order1.modified_time
	// >=:startDate and order1.modified_time <=:endDate and
	// order1.orderType<>'1' and order1.orderType<>'3'"
	// + "group by item.productName) UNION (select item.productName as
	// name,sum(item.piNum) as piNum, sum(item.totalMeter) as
	// totalMeter,sum(item.totalMeter*item.moneyNum) "
	// + "as moneyNum from tim.tim_salesoder_order_item as item,
	// tim.tim_salesoder_order as order1 where order1.deleted = 0 "
	// + staffSql
	// + " and order1.status='4' and order1.id = item.saleOrder_id "
	// + "and order1.customer_id=:customerid and order1.modified_time
	// >=:startDate and order1.modified_time <=:endDate and
	// order1.orderType<>'0' and order1.orderType<>'2'"
	// + "group by item.productName)) tt order by tt.name";
	//
	// Query query = this.getSession().createSQLQuery(hql);
	// Date sDate = DateUtil.getStartTime(startDate);
	// Date eDate = DateUtil.getEndTime(endDate);
	// query.setString("startDate", DateUtil.formatDateTime(sDate));
	// query.setString("endDate", DateUtil.formatDateTime(eDate));
	// query.setString("customerid", id);
	// if (!staffName.trim().equals("")) {
	// query.setString("creator", staffName);
	// }
	// return query.list();
	//
	// }

	/**
	 * 按客户统计
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> statSaleOrderByCustomer(String customerName,
			String staffName, Date startDate, Date endDate) {
		String customerSql = "";
		String staffSql = "";
		if (!customerName.trim().equals("")) {
			customerSql = " and po.customer.name like :customername";
		}

		if (!staffName.trim().equals("")) {
			staffSql = " and po.creator =:creator";
		}
		String hql = "select po.customer.name,po.customer.id,sum(po.piNum) as piNum, sum(po.meterNum) as meterNum,sum(po.moneyNum) as moneyNum"
				+ " from  "
				+ SaleOrder.class.getName()
				+ " as po where po.status='4' and po.deleted = 0"
				+ customerSql
				+ staffSql
				+ " and  po.modifyTime >=:startDate and po.modifyTime <=:endDate  group by po.customer.id ";

		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		if (!customerName.trim().equals("")) {
			query.setString("customername", "%" + customerName + "%");
		}
		// try {
		// staffName = new String(staffName.getBytes("ISO8859_1"),"utf-8");
		// } catch (UnsupportedEncodingException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }
		if (!staffName.trim().equals("")) {
			query.setString("creator", staffName);
		}
		System.out.println("名称：" + staffName);
		System.out.println("大小：" + query.list().size());
		return query.list();
	}

	public List<Object[]> getSimpleProductSummeryByCustomer(String customerid,
			String staffName, String startDate, String endDate) {
		String staffSql = "";
		if (!staffName.trim().equals("")) {
			staffSql = " and order.creator=:creator";
		}
		final String sql = "select item.productName,sum(item.piNum) as piNum,sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where order.deleted = 0 and order.status='4' and order.id = item.saleOrder.id and order.customer.id=:customerid"
				+ staffSql
				+ " and order.modifyTime >=:startDate and order.modifyTime <=:endDate group by item.productName ";

		Query query = this.getSession().createQuery(sql);
		Date sDate = DateUtil.getStartTime(DateUtil.strToDate(startDate));
		Date eDate = DateUtil.getEndTime(DateUtil.strToDate(endDate));

		query.setString("startDate", DateUtil.formatDateTime(sDate));// DateUtil.formatDateTime(sDate)
		query.setString("endDate", DateUtil.formatDateTime(eDate));// DateUtil.formatDateTime(eDate)
		query.setString("customerid", customerid);
		if (!staffName.trim().equals("")) {
			query.setString("creator", staffName);
		}
		return query.list();
	}

	// public List<Object[]> getSimpleProductSummeryByCustomer(String
	// customerid,
	// String staffName, String startDate, String endDate) {
	// String staffSql = "";
	// if (!staffName.trim().equals("")) {
	// staffSql = " and order1.creator=:creator";
	// }
	// final String sql = "select * from ((select item.productName as
	// name,sum(item.piNum) as piNum, sum(item.totalMeter) as
	// totalMeter,sum(item.totalMeter*item.moneyNum) "
	// + "as moneyNum from tim.tim_salesoder_order_item as item,
	// tim.tim_salesoder_order as order1 where order1.deleted = 0 "
	// + staffSql
	// + " and order1.status='4' and order1.id = item.saleOrder_id "
	// + "and order1.customer_id=:customerid and order1.modified_time
	// >=:startDate and order1.modified_time <=:endDate and
	// order1.orderType<>'1' and order1.orderType<>'3'"
	// + "group by item.productName) UNION (select item.productName as
	// name,sum(item.piNum) as piNum, sum(item.totalMeter) as
	// totalMeter,sum(item.totalMeter*item.moneyNum) "
	// + "as moneyNum from tim.tim_salesoder_order_item as item,
	// tim.tim_salesoder_order as order1 where order1.deleted = 0 "
	// + staffSql
	// + " and order1.status='4' and order1.id = item.saleOrder_id "
	// + "and order1.customer_id=:customerid and order1.modified_time
	// >=:startDate and order1.modified_time <=:endDate and
	// order1.orderType<>'0' and order1.orderType<>'2'"
	// + "group by item.productName)) tt order by tt.name";
	//
	// Query query = this.getSession().createSQLQuery(sql);
	// Date sDate = DateUtil.getStartTime(DateUtil.strToDate(startDate));
	// Date eDate = DateUtil.getEndTime(DateUtil.strToDate(endDate));
	//
	// query.setString("startDate", DateUtil.formatDateTime(sDate));//
	// DateUtil.formatDateTime(sDate)
	// query.setString("endDate", DateUtil.formatDateTime(eDate));//
	// DateUtil.formatDateTime(eDate)
	// query.setString("customerid", customerid);
	// if (!staffName.trim().equals("")) {
	// query.setString("creator", staffName);
	// }
	// return query.list();
	// }

	/**
	 * 按月份统计
	 * 
	 * @param year
	 * @param month
	 * @return
	 */
	public List<Object[]> statSaleOrderProductByMonth(int year, int month) {
		Date startDate = DateUtil.getFirstDayOfMonth(year, month);
		Date endDate = DateUtil.getLastDayOfMonth(year, month);
		String hql = "select item.productName,item.productColor,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0 and order.status='4' and  order.id = item.saleOrder.id  and order.modifyTime >=:startDate and order.modifyTime <=:endDate  group by item.product.id ";
		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();
	}

	/**
	 * 按月份统计
	 * 
	 * @param year
	 * @param month
	 * @return
	 */
	public List<Object[]> statSaleOrderCreatorByMonth(Date startDate,
			Date endDate) {

		String hql = "select order.creator,order.orderType,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0 and order.status='4' and  order.id = item.saleOrder.id  and order.modifyTime >=:startDate and order.modifyTime <=:endDate  group by order.creator ";
		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();
	}

	/**
	 * 获取销售订单流水号和Id
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getAllFlowNos() {
		String hql = " select saleOrderNum,id from "
				+ SaleOrder.class.getName() + " as s where s.deleted = 0";
		return this.getHibernateTemplate().find(hql);
	}

	public List<SaleOrder> getOrderByOrderId(long orderId) {
		Criteria ca = this.createUnDeletedCriteria();
		ca.add(Restrictions.eq("id", orderId));
		List result = ca.list();
		return result;
	}

	/**
	 * @author Harrison
	 * @param flowNo
	 * @return
	 */
	public SaleOrder getSaleOrderByFlowNo(String flowNo) {
		Criteria cte = createUnDeletedCriteria();
		cte.add(Restrictions.eq("flowNo", flowNo));
		return (SaleOrder) cte.uniqueResult();
	}

}
